Imports System.Data.OracleClient
Imports ccapps.Web.page

Namespace persistent
    Public Class Ihesar


        Public Shared Function GetCustomer(ByVal p_custcode As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_customer")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = p_custcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function



        Public Shared Function GetContractWithCustcode(ByVal p_custcode As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_contract_with_custcode")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = p_custcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetPackets(ByVal input_parameter As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_packets")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_input", OracleType.VarChar).Value = input_parameter
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function
        Public Shared Function getReturnReport(ByVal ReturnType As String, ByVal MsisdnCustcode As String, ByVal UserName As String, ByVal StartDate As String, ByVal FinishDate As String, ByVal Amount_1 As String, ByVal Amount_2 As String, ByVal caseid As String, ByVal returnID As String, ByRef message As String)
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.return_report")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)

            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_return_type", OracleType.VarChar).Value = ReturnType.Trim
                cmd.Parameters.Add("p_msisdn_custcode", OracleType.VarChar).Value = MsisdnCustcode.Trim
                cmd.Parameters.Add("p_users", OracleType.VarChar).Value = UserName.Trim
                cmd.Parameters.Add("p_start_date", OracleType.VarChar).Value = StartDate.Trim
                cmd.Parameters.Add("p_end_date", OracleType.VarChar).Value = FinishDate.Trim
                cmd.Parameters.Add("p_amount1", OracleType.VarChar).Value = Amount_1
                cmd.Parameters.Add("p_amount2", OracleType.VarChar).Value = Amount_2
                cmd.Parameters.Add("p_case_id", OracleType.VarChar).Value = caseid
                cmd.Parameters.Add("p_return_id", OracleType.VarChar).Value = returnID
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try


        End Function

        Public Shared Function getReturnReportForExcel(ByVal ReturnType As String, ByVal MsisdnCustcode As String, ByVal UserName As String, ByVal StartDate As String, ByVal FinishDate As String, ByVal Amount_1 As String, ByVal Amount_2 As String, ByVal caseid As String, ByVal returnID As String, ByRef message As String)
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.return_report_forexcel")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)

            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_return_type", OracleType.VarChar).Value = ReturnType.Trim
                cmd.Parameters.Add("p_msisdn_custcode", OracleType.VarChar).Value = MsisdnCustcode.Trim
                cmd.Parameters.Add("p_users", OracleType.VarChar).Value = UserName.Trim
                cmd.Parameters.Add("p_start_date", OracleType.VarChar).Value = StartDate.Trim
                cmd.Parameters.Add("p_end_date", OracleType.VarChar).Value = FinishDate.Trim
                cmd.Parameters.Add("p_amount1", OracleType.VarChar).Value = Amount_1
                cmd.Parameters.Add("p_amount2", OracleType.VarChar).Value = Amount_2
                cmd.Parameters.Add("p_case_id", OracleType.VarChar).Value = caseid
                cmd.Parameters.Add("p_return_id", OracleType.VarChar).Value = returnID
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try


        End Function


        Public Shared Function GetPacketsIndex(ByVal index As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_packets_index")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_index", OracleType.VarChar).Value = index
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function AddPacket(ByVal packetName As String, ByVal fivePercent As Char, ByVal eighteenPercent As Char, ByVal twentyfivePercent As Char, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(3) As String
            Dim l_Parameters_Val_Array(3) As String
            l_ProcedureName = "eu_bill.IHESAR.add_packet"
            Try
                l_Parameters_Def_Array(0) = "p_pname"
                l_Parameters_Def_Array(1) = "p_bes"
                l_Parameters_Def_Array(2) = "p_onsekiz"
                l_Parameters_Def_Array(3) = "p_yirmibes"
                l_Parameters_Val_Array(0) = packetName
                l_Parameters_Val_Array(1) = fivePercent
                l_Parameters_Val_Array(2) = eighteenPercent
                l_Parameters_Val_Array(3) = twentyfivePercent

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try


        End Function

        Public Shared Function UpdatePacket(ByVal packetid As String, ByVal packetName As String, ByVal fivePercent As Char, ByVal eighteenPercent As Char, ByVal twentyfivePercent As Char, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(4) As String
            Dim l_Parameters_Val_Array(4) As String
            l_ProcedureName = "eu_bill.IHESAR.update_packet"

            Try
                l_Parameters_Def_Array(0) = "p_pname"
                l_Parameters_Def_Array(1) = "p_bes"
                l_Parameters_Def_Array(2) = "p_onsekiz"
                l_Parameters_Def_Array(3) = "p_yirmibes"
                l_Parameters_Def_Array(4) = "p_pid"
                l_Parameters_Val_Array(0) = packetName
                l_Parameters_Val_Array(1) = fivePercent
                l_Parameters_Val_Array(2) = eighteenPercent
                l_Parameters_Val_Array(3) = twentyfivePercent
                l_Parameters_Val_Array(4) = packetid

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function
        Public Shared Function UpdateReturnType(ByVal returnTypeId As String, ByVal returnTypeNameNew As String, ByVal CdrFlag As Char, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(2) As String
            Dim l_Parameters_Val_Array(2) As String
            l_ProcedureName = "eu_bill.IHESAR.update_return_type"

            Try

                l_Parameters_Def_Array(0) = "p_return_type_id"
                l_Parameters_Def_Array(1) = "p_return_type_name_new"
                l_Parameters_Def_Array(2) = "p_cdr_flag"

                l_Parameters_Val_Array(0) = returnTypeId
                l_Parameters_Val_Array(1) = returnTypeNameNew
                l_Parameters_Val_Array(2) = CdrFlag

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function
        Public Shared Function UpdateReminder(ByVal reminderid As String, ByVal remindernamenew As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String
            l_ProcedureName = "eu_bill.IHESAR.update_reminder"

            Try

                l_Parameters_Def_Array(0) = "p_reminder_id"
                l_Parameters_Def_Array(1) = "p_reminder_new"
                l_Parameters_Val_Array(0) = reminderid
                l_Parameters_Val_Array(1) = remindernamenew


                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function

        Public Shared Function DeletePacket(ByVal packetid As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_packet"

                l_Parameters_Def_Array(0) = "p_pid"
                l_Parameters_Val_Array(0) = packetid

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False

            End Try

        End Function

        Public Shared Function GetReturnTypes(ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_return_types")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_input", OracleType.VarChar).Value = "NULL"
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetUsageSummary() As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_usage_summary")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_customerId", OracleType.VarChar).Value = "12302895"
                cmd.Parameters.Add("p_coid", OracleType.VarChar).Value = "13802785"
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = "23.12.2011"
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = "23.01.2012"
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function GetTariffHistory(ByVal msisdn As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tariff_history")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetTaahhutHistory(ByVal coid As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_taahhut_history")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_coid", OracleType.Number).Value = coid
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetPromotions(ByVal custcode As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_promotions")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function GetFreeUnits(ByVal custcode As String, ByVal startdate As String, ByVal enddate As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_free_units")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_start_date", OracleType.VarChar).Value = startdate
                cmd.Parameters.Add("p_end_date", OracleType.VarChar).Value = enddate
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetInvoiceCycles(ByVal custcode As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_invoice_cycles")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetUserNames(ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_user_names")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_input", OracleType.VarChar).Value = "NULL"
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString

            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function GetReturnReminders(ByVal input_parameter As String, ByVal message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.ihesar.get_return_reminder")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)

            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_return_type_id", OracleType.VarChar).Value = input_parameter
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString


            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function getCDRFlag(ByVal returnTypeId As String) As Boolean
            Dim result As Boolean = persistent.myDBConn.ORAFunction("eu_bill.IHESAR.get_cdr_flag", _
                                                New String() {"p_return_type_id"}, _
                                                New String() {returnTypeId})
            Return result

        End Function
        Public Shared Function GetSumInvoice(ByVal custcode As String, ByVal endDate As String) As String

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(2) As String
            Dim l_Parameters_Val_Array(2) As String
            Dim amount As String = ""
            l_ProcedureName = "Eu_bill.ihesar.get_invoice_items_sum"
            l_Parameters_Def_Array(0) = "p_custcode"
            l_Parameters_Def_Array(1) = "p_endDate"
            l_Parameters_Def_Array(2) = "p_amount"


            l_Parameters_Val_Array(0) = custcode
            l_Parameters_Val_Array(1) = endDate
            l_Parameters_Val_Array(2) = amount
            myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
            amount = l_Parameters_Val_Array(2)
            Return amount

        End Function

        Public Shared Function RefreshInvoiceSum(ByVal sessionId As String, ByVal custcode As String, ByRef message As String) As String

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(2) As String
            Dim l_Parameters_Val_Array(2) As String
            Dim amount As String = ""
            Try
                l_ProcedureName = "Eu_bill.ihesar.refresh_invoice_items_sum"
                l_Parameters_Def_Array(0) = "p_sessionid"
                l_Parameters_Def_Array(1) = "p_custcode"
                l_Parameters_Def_Array(2) = "p_amount"


                l_Parameters_Val_Array(0) = sessionId
                l_Parameters_Val_Array(1) = custcode
                l_Parameters_Val_Array(2) = amount
                myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                amount = l_Parameters_Val_Array(2)
                Return amount
            Catch ex As Exception
                message = ex.Message.ToString

            End Try

        End Function
        Public Shared Function addReturnReminder(ByVal reminderid As String, ByVal returntypeid As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_reminder_to_return"

                l_Parameters_Def_Array(0) = "p_reminder_id"
                l_Parameters_Def_Array(1) = "p_return_type_id"
                l_Parameters_Val_Array(0) = reminderid
                l_Parameters_Val_Array(1) = returntypeid



                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)

            Catch ex As Exception
                message = ex.Message.ToString
            End Try

            Return True

        End Function
        Public Shared Function addReturns(ByVal services As String, ByVal customerid As Integer, ByVal userid As String, ByVal totalreturn As String, ByVal returndescription As String, ByVal msisdn As String, ByVal caseid As String, ByVal actions As String, ByRef message As String)
            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(7) As String
            Dim l_Parameters_Val_Array(7) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_returns"

                l_Parameters_Def_Array(0) = "p_return_type"
                l_Parameters_Def_Array(1) = "p_customer_id"
                l_Parameters_Def_Array(2) = "p_user_id"
                l_Parameters_Def_Array(3) = "p_amount"
                l_Parameters_Def_Array(4) = "p_description"
                l_Parameters_Def_Array(5) = "p_msisdn"
                l_Parameters_Def_Array(6) = "p_case_id"
                l_Parameters_Def_Array(7) = "p_changes"
                l_Parameters_Val_Array(0) = services
                l_Parameters_Val_Array(1) = customerid
                l_Parameters_Val_Array(2) = userid
                l_Parameters_Val_Array(3) = totalreturn
                l_Parameters_Val_Array(4) = returndescription
                l_Parameters_Val_Array(5) = msisdn
                l_Parameters_Val_Array(6) = caseid
                l_Parameters_Val_Array(7) = actions


                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)

            Catch ex As Exception
                message = ex.Message.ToString
            End Try

            Return True
        End Function
        Public Shared Function getReminderTypes(ByVal message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_reminder")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_input", OracleType.VarChar).Value = "NULL"
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds

            Catch ex As Exception
                message = ex.Message.ToString


            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function

        Public Shared Function GetInvoiceItems(ByVal custcode As String, ByVal endDate As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_invoice_items")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function FillInvoiceItems(ByVal sessionid As String, ByVal custcode As String, ByVal endDate As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(2) As String
            Dim l_Parameters_Val_Array(2) As String
            Try
                l_ProcedureName = "eu_bill.IHESAR.fill_invoice_items"


                l_Parameters_Def_Array(0) = "p_sessionid"
                l_Parameters_Def_Array(1) = "p_custcode"
                l_Parameters_Def_Array(2) = "p_endDate"
                l_Parameters_Val_Array(0) = sessionid
                l_Parameters_Val_Array(1) = custcode
                l_Parameters_Val_Array(2) = endDate


                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try
        End Function


        Public Shared Function DeleteInvoiceItems(ByVal sessionid As String, ByVal custcode As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String
            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_invoice_items"

                l_Parameters_Def_Array(0) = "p_sessionid"
                l_Parameters_Def_Array(1) = "p_custcode"
                l_Parameters_Val_Array(0) = sessionid
                l_Parameters_Val_Array(1) = custcode

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function

        Public Shared Function RefreshInvoiceItems(ByVal sessionid As String, ByVal custcode As String, ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.refresh_invoice_items")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_sessionid", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function AddInvoiceData(ByVal itemName As String, ByVal amount As String, ByVal custcode As String, ByVal sessionid As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(3) As String
            Dim l_Parameters_Val_Array(3) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_invoice_data"

                l_Parameters_Def_Array(0) = "p_itemname"
                l_Parameters_Def_Array(1) = "p_amount"
                l_Parameters_Def_Array(2) = "p_custcode"
                l_Parameters_Def_Array(3) = "p_sessionid"
                l_Parameters_Val_Array(0) = itemName
                l_Parameters_Val_Array(1) = amount
                l_Parameters_Val_Array(2) = custcode
                l_Parameters_Val_Array(3) = sessionid

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function


        Public Shared Function UpdateInvoiceItem(ByVal custcode As String, ByVal sessionid As String, ByVal invoiceitem As String, ByVal amount As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(3) As String
            Dim l_Parameters_Val_Array(3) As String
            Try
                l_ProcedureName = "eu_bill.IHESAR.update_invoice_item"

                l_Parameters_Def_Array(0) = "p_custcode"
                l_Parameters_Def_Array(1) = "p_sessionid"
                l_Parameters_Def_Array(2) = "p_invoiceitem"
                l_Parameters_Def_Array(3) = "p_amount"
                l_Parameters_Val_Array(0) = custcode
                l_Parameters_Val_Array(1) = sessionid
                l_Parameters_Val_Array(2) = invoiceitem
                l_Parameters_Val_Array(3) = amount


                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False

            End Try

        End Function


        Public Shared Function GetCDR(ByVal msisdn As String, ByVal sessionid As String, ByVal startDate As String, ByVal endDate As String, ByRef rows As Integer, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_cdr")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate

                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try


        End Function


        Public Shared Function FillCDRTable(ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByRef result As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(4) As String
            Dim l_Parameters_Val_Array(4) As String
            Dim l_error_code As Integer
            Dim l_error_desc As String

            l_ProcedureName = "eu_bill.IHESAR.fill_cdr_table"

            Try
                l_Parameters_Def_Array(0) = "p_sessionid"
                l_Parameters_Def_Array(1) = "p_msisdn"
                l_Parameters_Def_Array(2) = "p_startDate"
                l_Parameters_Def_Array(3) = "p_endDate"
                l_Parameters_Def_Array(4) = "p_result"

                l_Parameters_Val_Array(0) = sessionid
                l_Parameters_Val_Array(1) = msisdn
                l_Parameters_Val_Array(2) = startDate
                l_Parameters_Val_Array(3) = endDate
                l_Parameters_Val_Array(4) = ""


                myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                result = l_Parameters_Val_Array(4)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False

            End Try
        End Function

        'Public Shared Function FillCDRTable(ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByRef message As String) As Boolean

        '    Dim l_ProcedureName As String
        '    Dim l_Parameters_Def_Array(3) As String
        '    Dim l_Parameters_Val_Array(3) As String
        '    Dim l_error_code As Integer
        '    Dim l_error_desc As String

        '    l_ProcedureName = "eu_bill.IHESAR.fill_cdr_table"

        '    Try
        '        l_Parameters_Def_Array(0) = "p_sessionid"
        '        l_Parameters_Def_Array(1) = "p_msisdn"
        '        l_Parameters_Def_Array(2) = "p_startDate"
        '        l_Parameters_Def_Array(3) = "p_endDate"
        '        l_Parameters_Val_Array(0) = sessionid
        '        l_Parameters_Val_Array(1) = msisdn
        '        l_Parameters_Val_Array(2) = startDate
        '        l_Parameters_Val_Array(3) = endDate


        '        myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)

        '        Return True
        '    Catch ex As Exception
        '        message = ex.Message.ToString
        '        Return False

        '    End Try
        'End Function


        Public Shared Function GetServiceNames(ByVal sessionid As String, ByVal msisdn As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_service_names")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function GetB_Tariffs(ByVal sessionid As String, ByVal msisdn As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_B_Tariffs")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function
        Public Shared Function GetZoneNames(ByVal sessionid As String, ByVal msisdn As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_zone_names")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


        Public Shared Function FilterCDR(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByRef rows As Integer, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.filter_CDR")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate

                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function FilterCDRFor_BTariff(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByVal bTariff As String, ByRef rows As Integer, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.filter_CDR_for_bTariff")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate
                cmd.Parameters.Add("p_bTariff", OracleType.VarChar).Value = bTariff

                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function FilterCDR2(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByVal bTariff As String, ByVal arananBolgeTanim As String, ByVal ucretliMi As String, ByRef rows As Integer, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.filter_CDR2")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate
                cmd.Parameters.Add("p_bTariff", OracleType.VarChar).Value = bTariff
                cmd.Parameters.Add("p_arananBolgeTanim", OracleType.VarChar).Value = arananBolgeTanim
                cmd.Parameters.Add("p_ucretliMi", OracleType.VarChar).Value = ucretliMi


                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function SortCDR(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByVal column As String, ByVal way As String, ByRef rows As Integer, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.sort_CDR")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate
                cmd.Parameters.Add("p_column_name", OracleType.VarChar).Value = column
                cmd.Parameters.Add("p_way", OracleType.VarChar).Value = way


                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                'LOGLAMA 
                Dim sorgu As String = "select * from dual"
                Dim gen As Object = New IhesarMainPage()
                persistent.myDBConn.myORATable_LOG(sorgu, "", "", "", msisdn, "select(SortCDR)", "Ihesar", gen.Session("user").ToString())

                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function
        Public Shared Function SortCDR2(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByVal column As String, ByVal way As String, ByRef rows As Integer, ByVal bTariff As String, ByVal arananBolgeTanim As String, ByVal ucretliMi As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.sort_CDR2")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate
                cmd.Parameters.Add("p_column_name", OracleType.VarChar).Value = column
                cmd.Parameters.Add("p_way", OracleType.VarChar).Value = way
                cmd.Parameters.Add("p_bTariff", OracleType.VarChar).Value = bTariff
                cmd.Parameters.Add("p_arananBolgeTanim", OracleType.VarChar).Value = arananBolgeTanim
                cmd.Parameters.Add("p_ucretliMi", OracleType.VarChar).Value = ucretliMi

                cmd.Parameters.Add("p_rows", OracleType.Int32).Direction = ParameterDirection.Output
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                rows = cmd.Parameters("p_rows").Value
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


        Public Shared Function GetSums(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByRef surebyte As Integer, ByRef roundedilmissure As Integer, ByRef ucretlenenmiktar As Double, ByRef message As String) As String()

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(8) As String
            Dim l_Parameters_Val_Array(8) As String

            l_ProcedureName = "eu_bill.IHESAR.get_sums"

            Try
                l_Parameters_Def_Array(0) = "p_servis"
                l_Parameters_Def_Array(1) = "p_zone"
                l_Parameters_Def_Array(2) = "p_sessionid"
                l_Parameters_Def_Array(3) = "p_msisdn"
                l_Parameters_Def_Array(4) = "p_startDate"
                l_Parameters_Def_Array(5) = "p_endDate"
                l_Parameters_Def_Array(6) = "p_surebyte"
                l_Parameters_Def_Array(7) = "p_roundedilmissure"
                l_Parameters_Def_Array(8) = "p_ucretlenenmiktar"

                l_Parameters_Val_Array(0) = servis
                l_Parameters_Val_Array(1) = zone
                l_Parameters_Val_Array(2) = sessionid
                l_Parameters_Val_Array(3) = msisdn
                l_Parameters_Val_Array(4) = startDate
                l_Parameters_Val_Array(5) = endDate
                l_Parameters_Val_Array(6) = ""
                l_Parameters_Val_Array(7) = ""
                l_Parameters_Val_Array(8) = ""


                myDBConn.ORAProcedure_INOUT(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)

                Dim result(2) As String
                surebyte = l_Parameters_Val_Array(5)
                roundedilmissure = l_Parameters_Val_Array(6)
                ucretlenenmiktar = l_Parameters_Val_Array(7)

                result(0) = surebyte
                result(1) = roundedilmissure
                result(2) = ucretlenenmiktar

                Return result
            Catch ex As Exception
                message = ex.Message.ToString

            End Try
        End Function



        Public Shared Function GetSum(ByVal servis As String, ByVal zone As String, ByVal sessionid As String, ByVal msisdn As String, ByVal startDate As String, ByVal endDate As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_sum")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_servis", OracleType.VarChar).Value = servis
                cmd.Parameters.Add("p_zone", OracleType.VarChar).Value = zone
                cmd.Parameters.Add("p_session", OracleType.VarChar).Value = sessionid
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_startDate", OracleType.VarChar).Value = startDate
                cmd.Parameters.Add("p_endDate", OracleType.VarChar).Value = endDate

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function



        Public Shared Function AllPromotions(ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.all_promotions")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function GetAllTariffs(ByRef message As String) As DataSet
            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.all_tariffs")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try
        End Function


        Public Shared Function GetRatingServices(ByVal tmcode As String, ByVal vscode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.rating_services")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


        Public Shared Function GetZones(ByVal tmcode As String, ByVal vscode As String, ByVal sncode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_zones")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode
                cmd.Parameters.Add("p_sncode", OracleType.VarChar).Value = sncode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


        Public Shared Function GetTimeZones(ByVal tmcode As String, ByVal vscode As String, ByVal sncode As String, ByVal zncode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tt")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                If tmcode = "" Or tmcode Is Nothing Or vscode = "" Or vscode Is Nothing Or sncode = "" Or sncode Is Nothing Or zncode = "" Or zncode Is Nothing Then
                    'Dim TZONEADI As System.Data.DataColumn
                    'Dim TZONEKODU As System.Data.DataColumn
                    'Dim tbl As DataTable
                    'ds.Tables.Add(tbl)
                    'ds.Tables("tbl").Columns.Add(TZONEADI)
                    'ds.Tables("tbl").Columns.Add(TZONEKODU)
                    Return Nothing
                Else
                    con.Open()
                    cmd.Connection = con
                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                    cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode
                    cmd.Parameters.Add("p_sncode", OracleType.VarChar).Value = sncode
                    cmd.Parameters.Add("p_zncode", OracleType.VarChar).Value = zncode
                    cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                    da.Fill(ds)
                    Return ds
                End If
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


        Public Shared Function GetAmount(ByVal tmcode As String, ByVal vscode As String, ByVal sncode As String, ByVal zncode As String, ByVal tzncode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_amount")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                If tmcode = "" Or tmcode Is Nothing Or vscode = "" Or vscode Is Nothing Or sncode = "" Or sncode Is Nothing Or zncode = "" Or zncode Is Nothing Or tzncode = "" Or tzncode Is Nothing Then
                    Return Nothing
                Else
                    con.Open()
                    cmd.Connection = con
                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                    cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode
                    cmd.Parameters.Add("p_sncode", OracleType.VarChar).Value = sncode
                    cmd.Parameters.Add("p_zncode", OracleType.VarChar).Value = zncode
                    cmd.Parameters.Add("p_ttcode", OracleType.VarChar).Value = tzncode
                    cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                    da.Fill(ds)
                    Return ds
                End If

            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function GetMonthlyAccessFee(ByVal tmcode As String, ByVal vscode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_monthly_accessfee")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function
        Public Shared Function AddReminder(ByVal reminderName As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_reminder"

                l_Parameters_Def_Array(0) = "p_reminder_name"
                l_Parameters_Val_Array(0) = reminderName

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function

        Public Shared Function DeleteReminder(ByVal reminderId As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_reminder"

                l_Parameters_Def_Array(0) = "p_reminder_id"
                l_Parameters_Val_Array(0) = reminderId

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function
        Public Shared Function AddReturnType(ByVal reminderName As String, ByVal cdrflag As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_return_type"

                l_Parameters_Def_Array(0) = "p_return_name"
                l_Parameters_Def_Array(1) = "p_cdr_flag"
                l_Parameters_Val_Array(0) = reminderName
                l_Parameters_Val_Array(1) = cdrflag

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function

        Public Shared Function DeleteRetRem(ByVal reminderName As String, ByVal returntypeid As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(1) As String
            Dim l_Parameters_Val_Array(1) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_return_reminder"

                l_Parameters_Def_Array(0) = "p_return_type_id"
                l_Parameters_Def_Array(1) = "p_reminder_name"
                l_Parameters_Val_Array(0) = returntypeid
                l_Parameters_Val_Array(1) = reminderName

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True
            Catch ex As Exception
                message = ex.Message.ToString
                Return False

            End Try

        End Function
        Public Shared Function DeleteReturnType(ByVal returnName As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_return_type"

                l_Parameters_Def_Array(0) = "p_return_type_id"
                l_Parameters_Val_Array(0) = returnName

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function


        Public Shared Function GetTariffPackets(ByVal tmcode As String, ByVal vscode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tariff_packets")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function GetTariffPacketsFees(ByVal tmcode As String, ByVal vscode As String, ByVal sncode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tariff_packets_fees")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_vscode", OracleType.VarChar).Value = vscode
                cmd.Parameters.Add("p_sncode", OracleType.VarChar).Value = sncode

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function
        Public Shared Function MakeZeroCdr(ByVal CdrId As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.make_zero_cdr"

                l_Parameters_Def_Array(0) = "p_cdr_id"
                l_Parameters_Val_Array(0) = CdrId

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try
        End Function

        Public Shared Function DeleteReturnReport(ByVal ReturnId As String, ByRef message As String) As Boolean


            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(0) As String
            Dim l_Parameters_Val_Array(0) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.delete_return_report"

                l_Parameters_Def_Array(0) = "p_return_id"
                l_Parameters_Val_Array(0) = ReturnId

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try
        End Function


        Public Shared Function GetTariffVscodes(ByVal tmcode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tariff_vscodes")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function FilterFreeUnits(ByVal custcode As String, ByVal msisdn As String, ByVal startdate As String, ByVal enddate As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.filter_free_units")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_custcode", OracleType.VarChar).Value = custcode
                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_start_date", OracleType.VarChar).Value = startdate
                cmd.Parameters.Add("p_end_date", OracleType.VarChar).Value = enddate

                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function GetTariffFreeunits(ByVal tmcode As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_tariff_freeunits")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_tmcode", OracleType.VarChar).Value = tmcode
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function

        Public Shared Function InsertLog(ByVal custcode As String, ByVal msisdn As String, ByVal username As String, ByVal userid As String, ByVal machinename As String, ByVal startdate As String, ByVal enddate As String, ByRef message As String) As Boolean

            Dim l_ProcedureName As String
            Dim l_Parameters_Def_Array(6) As String
            Dim l_Parameters_Val_Array(6) As String

            Try
                l_ProcedureName = "eu_bill.IHESAR.add_usage_log"

                l_Parameters_Def_Array(0) = "p_custcode"
                l_Parameters_Def_Array(1) = "p_msisdn"
                l_Parameters_Def_Array(2) = "p_username"
                l_Parameters_Def_Array(3) = "p_user_id"
                l_Parameters_Def_Array(4) = "p_machinename"
                l_Parameters_Def_Array(5) = "p_startdate"
                l_Parameters_Def_Array(6) = "p_enddate"
                l_Parameters_Val_Array(0) = custcode
                l_Parameters_Val_Array(1) = msisdn
                l_Parameters_Val_Array(2) = username
                l_Parameters_Val_Array(3) = "frombillingnet|" & userid
                l_Parameters_Val_Array(4) = machinename
                l_Parameters_Val_Array(5) = startdate
                l_Parameters_Val_Array(6) = enddate

                myDBConn.ORAProcedure(l_ProcedureName, l_Parameters_Def_Array, l_Parameters_Val_Array)
                Return True

            Catch ex As Exception
                message = ex.Message.ToString
                Return False
            End Try

        End Function

        Public Shared Function GetCoid(ByVal msisdn As String, ByRef message As String) As DataSet

            Dim con As New OracleConnection(ConfigurationSettings.AppSettings("OraConnStr"))
            Dim cmd As New OracleCommand("eu_bill.IHESAR.get_coid")
            Dim ds As New DataSet
            Dim da As New Data.OracleClient.OracleDataAdapter(cmd)
            Try
                con.Open()
                cmd.Connection = con
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("p_msisdn", OracleType.VarChar).Value = msisdn
                cmd.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output

                da.Fill(ds)
                Return ds
            Catch ex As Exception
                message = ex.Message.ToString
            Finally
                con.Close()
                cmd.Dispose()
                da.Dispose()
            End Try

        End Function


    End Class
End Namespace